﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace webapi.Models
{
  public class StudentService
  {

    //获取开设的课程
    static public DataRowCollection getCourseList()
    {
      string sql = "select b.name, a.course_id, a.class_course_id, b.point, a.day, a.time, a.start, a.last, a.count from set_up_course as a join course as b on a.course_id = b.course_id where a.id in (select max(id) from set_up_course group by course_id)";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }


    //根据id获取个人已选的课程
    static public DataRowCollection getOwnCourse(string id)
    {
      string sql = "select c.name, b.course_id, a.class_course_id, c.point, b.day, b.time, b.start, b.last, b.count from select_course as a join set_up_course as b on a.class_course_id = b.class_course_id join course as c on b.course_id = c.course_id where a.stu_id = '" + id + "'";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    //根据课程号获取课程信息
    static public DataRowCollection getCourseById(string id)
    {
      string sql = "select b.name, a.course_id, a.class_course_id, b.point, a.day, a.time, a.start, a.last, a.count from set_up_course as a join course as b on a.course_id = b.course_id where a.course_id = '" + id + "'";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    //根据开课班级号获取班级信息(获取班级剩余人数）
    static public DataRowCollection getSelectCourseByClassCourseId(string id)
    {
      string sql = "select * from select_course where class_course_id = '" + id + "'";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    //根据学号、开设班级号增加选课记录(选课)
    static public int addSelectCourse(string stu_id, string class_course_id, string time)
    {
      string sql = string.Format("insert into select_course(class_course_id, stu_id, select_time) values('{0}', '{1}', '{2}')", class_course_id, stu_id, time);
      return DB.ExecuteSql(sql);
    }

    //根据学号、开设班级号删除选课记录（退课）
    static public int deleteSelectCourse(string stu_id, string class_course_id)
    {
      string sql = string.Format("delete from select_course where class_course_id='{0}' and stu_id='{1}'", class_course_id, stu_id);
      return DB.ExecuteSql(sql);
    }


    internal static bool isSelectCourse(string class_course_id, string id)
    {
      string sql = string.Format("select count(*) from select_course where class_course_id='{0}' and stu_id='{1}'", class_course_id, id);
      return (int)DB.ExecuteSelectSql(sql).Tables[0].Rows[0][0] > 0;
    }
  }
}